from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from threading import Thread
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
from sql import sqlseverDB
from config import *
import tkinter.messagebox as messagebox
import openpyxl,time,concurrent.futures,csv

class Data4Fra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        Label(self, text='入仓功能', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=2)
        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=2)
        #DCBMFrame(self).pack(fill=X,padx=2,pady=2)
        AdDataComFrame(self).pack(fill=X,padx=2,pady=2)
        ADAutoFrame(self).pack(fill=X,padx=2,pady=2)
        CJDPAutoFrame(self).pack(fill=X,padx=2,pady=2)

class DCBMFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.bmCount=IntVar()
        self.rbmCount=IntVar()
        Thread(target=self.getkwcount).start()
        self.createWidgets()

    def createWidgets(self):

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='虾皮大数据补充资料', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)
        f00=Frame(f0)
        f00.pack(fill=X,side=LEFT,padx=2,pady=2)
        self.dcbtn=Button(f00,text='导出入仓数据补充商家编码',command=lambda:Thread(target=self.start_dc).start(),fg='#ffffff',bg='green',width=20)
        self.dcbtn.pack(side=LEFT, padx=2, pady=2)
        Label(f00, textvariable=self.bmCount,fg='red',width=3).pack(side=LEFT, padx=2, pady=2)

        f01=Frame(f0)
        f01.pack(fill=X,side=LEFT,padx=6,pady=2)

        Label(f01, text='已填商家编码文件路径:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f01)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f01, textvariable=self.rbmCount,fg='red',width=3).pack(side=LEFT, padx=2, pady=2)
        Button(f01,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)
        self.drbtn=Button(f01,text='上传入仓数据商家编码',command=lambda:Thread(target=self.start_dr).start(),fg='#ffffff',bg='green',width=20)
        self.drbtn.pack(side=LEFT, padx=2, pady=2)

    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.rbmCount.set(max_row-1)
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    

    def getkwcount(self):
        while True:
            try:
                rs=self.ms.ExecQuery('''SELECT COUNT(DISTINCT itemid) from ProShopeePh where isCjRcUrl=1 and sjbm is null''')
                self.bmCount.set(rs[0][0])
                time.sleep(10)
            except Exception as e:
                print(e)
    
    def start_dc(self):

        self.dcbtn.config(state=DISABLED)
        sqlstr='''
                    select 
                    itemid as 产品id,
                    yntitle as 印尼标题,
                    [image] as 主图,
                    newmaxprice as 印尼链接最贵SKU新价格,
                    sjbm as 商家编码
                    from ProShopeePh
                    where id in(select MIN(id) from ProShopeePh where itemid in (select DISTINCT itemid from ProShopeePh 
                    where isCjRcUrl=1 and sjbm is null) 
                    group by itemid)
                '''

        dczt=sqlToExcel('需补充商家编码',self.ms,sqlstr)
        if not dczt:
            print('当前无需补充商家编码')
            messagebox.showinfo('提示',f'当前无需补充商家编码')
        else:
            print(f'成功导出到《{dczt}》')
            messagebox.showinfo('提示',f'成功导出到《{dczt}》')

        self.dcbtn.config(state=NORMAL)


    def start_dr(self):

        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先导入表格')
            return
        self.drbtn.config(state=DISABLED)
        pdatas=getDictDatasByEecel2(self.fpath)
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures=[]
            row=2
            for pdata in pdatas:

                cur_pid=pdata['产品id']
                cur_sjbm=pdata['商家编码']


                futures.append(executor.submit(self.upsjbm,row,[cur_sjbm,cur_pid]))
                row+=1

            for future in concurrent.futures.as_completed(futures):
                try:
                    res_row,res_itemid,res_sjbm,res_aff = future.result()
                    print(f'第 {res_row} 行,ItemId:{res_itemid},商家编码:《{res_sjbm}》,更新sku数:{res_aff}')
                except Exception as exc:
                    print(f"线程出错: {exc}")
        print('商家编码上传完毕')
        messagebox.showinfo('提示','商家编码上传完毕')
        self.drbtn.config(state=NORMAL)

    def upsjbm(self,row,params):
        sjbm,itemid=params
        aff=ms.ExecNoQuery('update ProShopeePh set sjbm=? where itemid=?',params)

        return (row,itemid,sjbm,aff)

class AdDataComFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.adCount=IntVar()
        self.radCount=IntVar()
        Thread(target=self.getkwcount).start()
        self.createWidgets()

    def createWidgets(self):


        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='手动补充广告表资料', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)
        f00=Frame(f0)
        f00.pack(fill=X,side=LEFT,padx=2,pady=2)
        self.dcbtn=Button(f00,text='导出手动补充资料',command=lambda:Thread(target=self.start_dc).start(),fg='#ffffff',bg='green',width=20)
        self.dcbtn.pack(side=LEFT, padx=2, pady=2)
        Label(f00, textvariable=self.adCount,fg='red',width=3).pack(side=LEFT, padx=2, pady=2)

        f01=Frame(f0)
        f01.pack(fill=X,side=LEFT,padx=6,pady=2)

        Label(f01, text='已填商家编码文件路径:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f01)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f01, textvariable=self.radCount,fg='red',width=3).pack(side=LEFT, padx=2, pady=2)
        Button(f01,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)
        self.drbtn=Button(f01,text='上传手动补充资料',command=lambda:Thread(target=self.start_dr).start(),fg='#ffffff',bg='green',width=20)
        self.drbtn.pack(side=LEFT, padx=2, pady=2)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        ADFrame(f1).pack(side=LEFT)

    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.radCount.set(max_row-1)
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    
    def getkwcount(self):
        while True:
            try:
                rs=self.ms.ExecQuery('''SELECT COUNT(DISTINCT ShopId) from ADpro where SJBM is NULL''')
                self.adCount.set(rs[0][0])
                time.sleep(10)
            except Exception as e:
                print(e)
    
    def start_dc(self):

        self.dcbtn.config(state=DISABLED)
        sqlstr='''
                    select
                    ShopID,
                    Purl,
                    SJName as 商家名字,
                    YYName as 运营名字,
                    DpUrl as 店铺首页网址,
                    sjbm as 商家编码
                    from ADPro
                    where Id in (select min(Id) from ADPro where SJBM is null group by ShopID)
                '''
        fname_qz='需手动补充资料'
        dczt=sqlToExcel(fname_qz,self.ms,sqlstr)
        if not dczt:
            print(f'当前无{fname_qz}')
            messagebox.showinfo('提示',f'当前无{fname_qz}')
        else:
            print(f'成功导出到《{dczt}》')
            messagebox.showinfo('提示',f'成功导出到《{dczt}》')

        self.dcbtn.config(state=NORMAL)

    def start_dr(self):

        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先导入表格')
            return
        self.drbtn.config(state=DISABLED)
        pdatas=getDictDatasByEecel2(self.fpath)
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures=[]
            row=2
            for pdata in pdatas:

                cur_shopid=pdata['ShopID']
                cur_sjname=pdata['商家名字']
                cur_yyname=pdata['运营名字']
                cur_dpurl=pdata['店铺首页网址']
                cur_sjbm=pdata['商家编码']


                futures.append(executor.submit(self.up_ad_info,row,[cur_shopid,cur_sjname,cur_yyname,cur_dpurl,cur_sjbm]))
                row+=1

            for future in concurrent.futures.as_completed(futures):
                try:
                    res_row,res_shopid,res_sjname,res_yyname,res_dpurl,res_sjbm,res_aff = future.result()
                    print(f'''第 {res_row} 行,ShopId:{res_shopid},补充资料 =>
                        商家名字:《{res_sjname}》,
                        运营名字:《{res_yyname}》,
                        店铺网址:《{res_dpurl}》,                         
                        商家编码:《{res_sjbm}》,
                        更新行数:{res_aff}
                        ''')
                except Exception as exc:
                    print(f"线程出错: {exc}")
        print('手动补充资料上传完毕')
        messagebox.showinfo('提示','手动补充资料上传完毕')
        self.drbtn.config(state=NORMAL)

    def up_ad_info(self,row,params):
        cur_shopid,cur_sjname,cur_yyname,cur_dpurl,cur_sjbm=params
        aff=ms.ExecNoQuery('update ADpro set SJName=?,YYName=?,DpUrl=?,sjbm=? where ShopId=?',
                           [cur_sjname,cur_yyname,cur_dpurl,cur_sjbm,cur_shopid])
        return (row,cur_shopid,cur_sjname,cur_yyname,cur_dpurl,cur_sjbm,aff)

class ADFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self, master)
        self.ms=sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.RowCount=IntVar()
        self.createWidgets()

    def createWidgets(self):
        f0=Frame(self)
        f0.pack(fill=X,pady=2,padx=2)
        Label(f0, text='导入广告表:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f0)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f0,text='选择文件',fg='#ffffff',bg='green',command=lambda:Thread(target=self.choosefile).start()).pack(side=LEFT,padx=2, pady=2)
        Label(f0, textvariable=self.RowCount,fg='red',width=4,justify='left').pack(side=LEFT, pady=2)
        self.btnDrad=Button(f0,text='上传广告',fg='#ffffff',bg='green',command=lambda:Thread(target=self.addr).start())
        self.btnDrad.pack(side=LEFT,padx=2, pady=2)

    def choosefile(self):

        select_path=filedialog.askopenfilename()
        if select_path.endswith('.csv'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path

            with open(select_path, newline='',encoding='utf-8-sig') as csvfile:
                reader = csv.reader(csvfile)
                self.adDatas = list(reader)
                self.RowCount.set(len(self.adDatas))
        else:
            messagebox.showerror('错误提示','请选择csv文件')
    
    def addr(self):

        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先导入广告表')
            return
        self.btnDrad.config(state=DISABLED)
        adDatas=self.adDatas
        line_row=0
        for line in adDatas:
            if len(line)==0:
                line_row+=1
                continue
            if line[0]=='Shop ID':
                shopid_ind=line_row
            if line[0]=='Sequence':
                title_ind=line_row
                break
            line_row+=1

        shopid=adDatas[shopid_ind][1]
        titles=adDatas[title_ind]
        titles =[re.sub(r'[\s/]+','',t) for t in titles]
        titles=titles[1:]+['ShopID']
        titles=replace_first_occurrence(titles,'AdName','ProductNameAdName')
        titles=replace_first_occurrence(titles,'KeywordLocation','PlacementKeyword')
        self.titles=titles
        pdatas=[dict(zip(titles,pdata[1:]+[shopid])) for pdata in adDatas[title_ind+1:]]
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures=[]
            row=title_ind+2
            for pdata in pdatas:
                cparams=[]
                cur_BiddingMethod=pdata['BiddingMethod']
                cur_Placement=pdata['Placement']
                if not (cur_BiddingMethod.lower()=='Manual Bidding'.lower() and cur_Placement.lower()=='Search'.lower()):
                    row+=1
                    continue
                cur_pid=pdata['ProductID']
                cur_kw=pdata['PlacementKeyword']
                cur_imp=pdata['Impression']
                cur_imp =int(cur_imp) if cur_imp else 0
                for k,v in pdata.items():
                    if k=='BiddingMethod' or k=='Placement':
                        continue
                    if v=='-':
                        v=None
                    cpara=v
                    if 'Date' in k:
                        cpara=convert_to_timestamp(v)
                    elif v and '%' in v and '.' in v:
                        cpara=float(v.replace('%',''))
                    
                    cparams.append(cpara)
                futures.append(executor.submit(self.upad,row,cur_pid,cur_kw,cur_imp,cparams))
                row+=1
            for future in concurrent.futures.as_completed(futures):
                try:
                    res_mes,res_row,res_pid,res_kw,res_aff = future.result()
                    print(f'第 {res_row} 行,ProductID:{res_pid},kw:《{res_kw}》,{res_mes},状态:{res_aff}')
                except Exception as exc:
                    print(f"线程出错: {exc}")

        print('广告数据上传完毕')
        messagebox.showinfo('提示','广告数据上传完毕')

        self.btnDrad.config(state=NORMAL)
    
    def upad(self,row,pid,kw,imp,params):
        
        rs1=ms.ExecQuery('select StartDate,Impression from adpro where ProductID=? and PlacementKeyword=?',(pid,kw))
        
        titles=[f'[{tt}]'for tt in self.titles]
        titles.remove('[BiddingMethod]')
        titles.remove('[Placement]')
        wstr=','.join(['?' for _ in params])
        if len(rs1)==0:
            zdstr=','.join(titles)
            sqlstr=f'insert into [ADPro]({zdstr}) values ({wstr})'
            
            aff=ms.ExecNoQuery(sqlstr,params)

            return ('增加数据',row,pid,kw,aff)

        else:
            start_date,old_imp=rs1[0]
            days_diff=get_days_diff_from_timestamps(start_date,time.time())
            old_imp=old_imp if old_imp else 0
            sqlstr1='update [ADPro] set IsImpressionAdd=0 where ProductID=? and PlacementKeyword=?'
            zdstr=','.join([f'{tt}=?' for tt in titles])
            sqlstr2=f'update [ADPro] set {zdstr},IsImpressionAdd=1 where ProductID=? and PlacementKeyword=?'
            if days_diff>30:

                if imp<old_imp and imp>0:
                    sqlstr=sqlstr1
                    cur_para=(pid,kw)
                    msg='只更新IsImpressionAdd为0'
                elif imp<=old_imp and imp==0:
                    sqlstr=sqlstr1
                    cur_para=(pid,kw)
                    msg='只更新IsImpressionAdd为0'
                elif imp>=old_imp and imp>0:
                    sqlstr=sqlstr2
                    cur_para=params+[pid,kw]
                    msg='更新所有参数'
                else:
                    return ('忽略本行',row,pid,kw,-1)

            else:
                if imp<old_imp and imp>0:
                    sqlstr=sqlstr1
                    cur_para=(pid,kw)
                    msg='只更新IsImpressionAdd为0'
                elif imp<=old_imp and imp==0:
                    sqlstr=sqlstr1
                    cur_para=(pid,kw)
                    msg='只更新IsImpressionAdd为0'
                elif imp>old_imp:
                    sqlstr=sqlstr2
                    cur_para=params+[pid,kw]
                    msg='更新所有参数'

                else:
                    return ('忽略本行',row,pid,kw,-1)

            aff=ms.ExecNoQuery(sqlstr,cur_para)
            return (msg,row,pid,kw,aff)

class ADAutoFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.adCount=StringVar()
        self.adCount.set('正在获取广告表搜索量任务状态...')
        self.radCount=StringVar()
        self.radCount.set('正在获取广告表任务状态...')
        Thread(target=self.getcount).start()
        self.createWidgets()

    def createWidgets(self):
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='采集广告表上面参数的最新信息', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)
        
        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=Y,side=LEFT)
        f000=Frame(f00)
        f000.pack(fill=X)
        Label(f000, textvariable=self.adCount,fg='red',anchor='w',width=25).pack(side=LEFT,padx=2, pady=2)
        self.ssbtn=Button(f000,text='开启查询搜索量',state=DISABLED,command=lambda:Thread(target=self.start_cat).start(),fg='#ffffff',bg='green',width=15)
        self.ssbtn.pack(side=LEFT,padx=2, pady=2)
        f001=Frame(f00)
        f001.pack(fill=X,pady=5)
        Label(f001, textvariable=self.radCount,fg='red',anchor='w',width=25).pack(side=LEFT,padx=2, pady=2)
        self.dptaskbtn=Button(f001,text='开启查询店铺',state=DISABLED,command=lambda:Thread(target=self.start_cdp).start(),fg='#ffffff',bg='green',width=15)
        self.dptaskbtn.pack(side=LEFT,padx=2, pady=2)

        f01=Frame(f1)
        f01.pack(fill=Y,side=RIGHT)
        DCHihtKwFrame(f01)

        f2=Frame(self)
        f2.pack(fill=X,padx=2,pady=2)
        TaskTableFrame(f2)

    
    def getcount(self):

        while True:
            try:
                rs0=self.ms.ExecQuery('select [state],[tcount] from [task] order by [id]')
                if rs0[0][0]==0:
                    self.dptaskbtn.config(state=NORMAL)
                    self.radCount.set('采集广告表店铺未开启')
                else:
                    self.dptaskbtn.config(state=DISABLED)
                    self.radCount.set(f'执行中,剩余:{rs0[0][1]}')
                
                if rs0[1][0]==0:
                    
                    self.ssbtn.config(state=NORMAL)
                    self.adCount.set('获取广告表关键词搜索量未开启')
                else:
                    self.ssbtn.config(state=DISABLED)
                    self.adCount.set(f'执行中,剩余:{rs0[1][1]}')
                
            except Exception as e:
                pass

            time.sleep(10)

    def start_cat(self):
        
        if not messagebox.askyesno('提示','是否开启查询搜索量任务'):
            return

        self.ssbtn.config(state=DISABLED)
        print('正在往服务器发布查询搜索量任务....')
        rs=ms.ExecQuery(f'select [id],[PlacementKeyword] from [ADPro]')
        datas=[[2,r[0],r[1]] for r in rs]
        aff1=ms.insert_datas(datas,'tasklist',('TaskId','fromAdId','kw'))
        sqlstr='''
                update [Task] set state=1 where [id]=2
            '''
        
        aff2=self.ms.ExecNoQuery(sqlstr)
        if aff1>0 and aff2>0:
            print(f'发布查询搜索量任务成功')
        else:
            print(f'发布查询搜索量任务失败')
            return
        firstbh=True
        while True:
            rs=self.ms.ExecQuery('select [tcount] from [task] where [id]=2')
            cur_tcount=rs[0][0]
            if not firstbh:
                print(f'查询搜索量任务队列剩余:{cur_tcount}')
            if cur_tcount>0 and firstbh:
                firstbh=False
            if not firstbh and cur_tcount==0:
                break
            time.sleep(10)
      
    def start_cdp(self):
        if not messagebox.askyesno('提示','是否开启采集印尼店铺任务'):
            return
        self.dptaskbtn.config(state=DISABLED)
        print('正在往服务器发布采集店铺任务....')
        
        sqlstr='''
                update [Task] set state=1 where [id]=1
            '''
        
        aff=self.ms.ExecNoQuery(sqlstr)
        if aff>0:
            print(f'发布采集店铺任务成功')
        else:
            print(f'发布采集店铺任务失败')
        firstbh=True
        while True:
            rs=self.ms.ExecQuery('select [tcount] from [task] where [id]=1')
            cur_tcount=rs[0][0]
            if not firstbh:
                print(f'采集店铺任务队列剩余:{cur_tcount}')
            if cur_tcount>0 and firstbh:
                firstbh=False
            if not firstbh and cur_tcount==0:
                break
            time.sleep(10)
    
class CJDPAutoFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.rbmCount=IntVar()
        self.statusStr=StringVar()
        self.statusStr.set('正在获取采集店铺任务状态...')
        Thread(target=self.getcount).start()
        self.createWidgets()

    def createWidgets(self):
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='采集印尼铺货店铺链接', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=X,side=LEFT,padx=2,pady=2)
        f001=Frame(f00)
        f001.pack(fill=X)
        Label(f001, text='店铺链接文件地址:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f001)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f001, textvariable=self.rbmCount,fg='red',width=3).pack(side=LEFT, padx=2, pady=2)
        Button(f001,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)

        self.drbtn=Button(f001,text='采集铺货链接',state=DISABLED,command=lambda:Thread(target=self.start_dr).start(),fg='#ffffff',bg='green',width=20)
        self.drbtn.pack(side=LEFT,padx=2, pady=2)
        Label(f001, textvariable=self.statusStr,fg='red').pack(side=LEFT, padx=2, pady=2)



    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.rbmCount.set(max_row-1)
        else:
            messagebox.showerror('错误提示','请选择excel文件')
            
    def getcount(self):
        while True:
            try:
                rs1=self.ms.ExecQuery('select [tcount] from [task] where [id]=3')
                if rs1[0][0]==0:
                    self.drbtn.config(state=NORMAL)
                    self.statusStr.set('采集广告表店铺未开启')

                else:
                    self.drbtn.config(state=DISABLED)
                    self.statusStr.set(f'执行中,剩余:{rs1[0][0]}')
                
                time.sleep(5)
            except Exception as e:
                pass
    
    def start_dr(self):
        
        if not self.DaoRuIpt.get():
            messagebox.showinfo('提示','请先导入路径')
            return

        if not messagebox.askyesno('提示','是否开启采集印尼店铺任务'):
            return
        self.drbtn.config(state=DISABLED)
        self.ms.ExecNoQuery('update [task] set state=1 where [id]=3' )
        print('正在往服务器发布采集店铺任务....')
        dp_datas=getDictDatasByEecel2(self.fpath)
        params=[]
        for dp_data in dp_datas:
            dpname=dp_data['印尼虾皮店铺名称']
            dpuser=dp_data['印尼虾皮登录用户名']
            dpurl=dp_data['印尼虾皮店铺网址']
            if '一次铺货' in dp_data['链接类型']:
                dptype=1
            elif '二次铺货' in dp_data['链接类型']:
                dptype=2
            elif '入仓' in dp_data['链接类型']:
                dptype=3
                
            params.append([str(dpname),str(dpuser),dpurl,dptype,3,0])
        aff2=self.ms.insert_datas(params,'tasklist',('dpname','dpuser','dpurl','dptype','taskid','page'))
        if  aff2>0:
            print(f'发布采集店铺任务成功')
        else:
            print(f'发布采集店铺任务失败')
            return
        firstbh=True
        while True:
            rs=self.ms.ExecQuery('select count(*) from [taskList] where [taskid]=3 and [id]=3')
            cur_tcount=rs[0][0]
            if not firstbh:
                print(f'采集店铺任务队列剩余:{cur_tcount}')
            if cur_tcount>0 and firstbh:
                firstbh=False
            if not firstbh and cur_tcount==0:
                break
            time.sleep(10)

class TaskTableFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.pack(fill=X)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.createWidgets()

    def createWidgets(self):
        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=2)
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=X,side=LEFT,padx=2,pady=2)
        f000=Frame(f00)
        f000.pack(fill=X,pady=5)
        Label(f000, text='搜索量小于:').pack(side=LEFT, padx=2, pady=2)
        self.SearchCountIpt = Entry(f000,width=10)
        self.SearchCountIpt.pack(side=LEFT, padx=2, pady=2)

        Label(f000, text='CTR区间:').pack(side=LEFT, padx=2, pady=2)
        self.CTRMinIpt = Entry(f000,width=10)
        self.CTRMinIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f000, text='~').pack(side=LEFT,padx=2, pady=2)
        self.CTRMaxIpt = Entry(f000,width=10)
        self.CTRMaxIpt.pack(side=LEFT, padx=2, pady=2)
        

        f002=Frame(f00)
        f002.pack(fill=X)
        self.dcbtn=Button(f002,text='导出点击任务表',command=lambda:Thread(target=self.start_dc).start(),fg='#ffffff',bg='green')
        self.dcbtn.pack(side=LEFT,padx=2, pady=2)

        f01=Frame(f1)
        f01.pack(fill=Y,side=RIGHT)
        PidSearchFrame(f01)

        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=2)

        f2=Frame(self)
        f2.pack(fill=X,side=LEFT)
        ClickUpFrame(f2)

    def start_dc(self):

        if (not self.CTRMinIpt.get().strip()) or (not self.CTRMaxIpt.get().strip()):
            messagebox.showinfo('提示','请输入完整CTR区间')
            return
        self.dcbtn.config(state=DISABLED)
        minctr=float(self.CTRMinIpt.get().strip())
        maxctr=float(self.CTRMaxIpt.get().strip())
        SearchCount=self.SearchCountIpt.get().strip()
        Serstr=f'and SearchCount<{SearchCount}' if SearchCount else ''
        sqlstr=f'''
            SELECT
                ClickQT,
                PlacementKeyword 关键词,
                SearchCount 搜索量,
                PageTotal 产品总页数,
                PositionZH 按综合位置,
                PositionXL 按销量位置,
                SoldMonth 月销量,
                RatingStar 评分,
                CmtCount 评价数量,
                SoldTotal 总销量,
                SJName 商家名字,
                Purl 产品链接,
                ProductNameAdName 标题,
                PriceMin 最低价
            FROM 
                Adpro
            WHERE 
                ClickOrNot = 1
                and IsImpressionAdd!=1
                and Impression>=1
                and CTR between ? and ?
                {Serstr}
            ORDER BY 
                productid,CTR desc
            '''
        print('正在链接数据库...')
        rs= ms.ExecQuerydict(sqlstr,(minctr,maxctr))
        
        datas={}
        row_count=0
        for r in rs:
            cur_purl=r['产品链接']
            cur_kw=r['关键词']
            cur_title=r['标题']
            cur_dj=r['ClickQT']
            isbz=True
            kw_words=[item.lower() for item in cur_kw.split()]
            title_words=[item.lower() for item in cur_title.split()]

            for ckw in kw_words:
                if ckw.isdigit():
                    continue
                if ckw not in title_words:
                    isbz=False
                    break
            if isbz:
                r.pop('ClickQT')
                if not datas.get(cur_purl):

                    datas[cur_purl]=[cur_dj,[r]]
                else:
                    datas[cur_purl][1].append(r)
                row_count+=1
        print(f'获取过滤数据:{row_count}行')
        dj_datas=[]

        for cpurl,cr in datas.items():

            cur_dj,cdatas=cr
            if not cur_dj:
                dj_datas.extend(cdatas)
            else:
                if cur_dj<=len(cdatas):
                    task_datas=cdatas[:cur_dj]
                    
                else:
                    repeat_datas=cdatas*(math.ceil(cur_dj/len(cdatas)))
                    task_datas=repeat_datas[:cur_dj]
                
                dj_datas.extend(task_datas)
                print(f'链接:{cpurl},关键词数量:{len(cdatas)},ClickQT:{cur_dj},生成点击行数:{len(task_datas)}')

        print(f'总点击行数: {len(dj_datas)}')

        fname=crate_excel('点击任务表',dj_datas)
        if fname:
            print(f'成功导出点击任务表到 ：《{fname}》')
            messagebox.showinfo('提示',f'成功导出点击任务表到 ：《{fname}》')

        self.dcbtn.config(state=NORMAL)

class ClickUpFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.pack(fill=X,padx=2)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.purlcount=IntVar()
        self.rbmCount=IntVar()
        Thread(target=self.getcount).start()
        self.createWidgets()

    def createWidgets(self):
        f0=Frame(self)
        f0.pack(fill=X)

        f01=Frame(f0)
        f01.pack(fill=X,padx=2,pady=2)

        self.btn_dc=Button(f01,text='导出修改点击参数',command=lambda:Thread(target=self.start_dc).start(),fg='#ffffff',bg='green')
        self.btn_dc.pack(side=LEFT,padx=2, pady=2)
        Label(f01, textvariable=self.purlcount,fg='red',width=2).pack(side=LEFT, padx=2, pady=2)

        Label(f01, text='路径:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f01,width=10)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f01, textvariable=self.rbmCount,fg='red',width=3).pack(side=LEFT, padx=2, pady=2)
        Button(f01,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)

        self.btn_dr=Button(f01,text='导入修改点击参数',command=lambda:Thread(target=self.start_dr).start(),fg='#ffffff',bg='green')
        self.btn_dr.pack(side=LEFT, padx=2, pady=2)


    def getcount(self):
        while True:
            try:
                rs=self.ms.ExecQuery('''SELECT COUNT(DISTINCT ProductID) from ADPro where ClickOrNot=1''')
                self.purlcount.set(rs[0][0])
                time.sleep(10)
            except Exception as e:
                print(e)

    def start_dc(self):
        self.btn_dc.config(state=DISABLED)

        sqlstr='''
            select
            ProductNameAdName,
            Purl,
            SJBM,
            SJName,
            YYName,
            ClickQT,
            ClickOrNot
            from ADPro
            where [id] in (select min(id) from ADPro group by purl) order by SJName
        ''' 
        fname=sqlToExcel('需修改点击参数',self.ms,sqlstr)
        if fname:
            print(f'成功导出:《{fname}》')
            messagebox.showinfo('提示',f'成功导出:《{fname}》')
        
        else:
            print('导出失败')
        self.btn_dc.config(state=NORMAL)
    
    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.rbmCount.set(max_row-1)
        else:
            messagebox.showerror('错误提示','请选择excel文件')

    def start_dr(self):

        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先导入表格')
            return
        self.btn_dr.config(state=DISABLED)
        pdatas=getDictDatasByEecel2(self.fpath)
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures=[]
            row=2
            for pdata in pdatas:

                cur_purl=pdata['Purl']
                cur_ClickQT=pdata['ClickQT']
                if not cur_ClickQT:
                    cur_ClickQT=None
                cur_ClickOrNot=pdata['ClickOrNot']
                futures.append(executor.submit(self.updj,row,[cur_purl,cur_ClickQT,cur_ClickOrNot]))
                row+=1

            for future in concurrent.futures.as_completed(futures):
                try:
                    res_row,res_purl,res_aff = future.result()
                    print(f'第 {res_row} 行,purl:{res_purl},更新行数:{res_aff}')
                except Exception as exc:
                    print(f"线程出错: {exc}")
        print('点击参数上传完毕')
        messagebox.showinfo('提示','点击参数上传完毕')
        self.btn_dr.config(state=NORMAL)
    
    def updj(self,row,params):
        purl,ClickQT,ClickOrNot=params
        aff=ms.ExecNoQuery('update AdPro set ClickQT=?,ClickOrNot=? where Purl=?',(ClickQT,ClickOrNot,purl))

        return (row,purl,aff)
    
class PidSearchFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.pack(fill=X,padx=5)
        self.cookiestr=''
        self.datas=[]
        self.statustxt=IntVar()
        self.createWidgets()
    def createWidgets(self):

        self.ipF2=Frame(self)
        self.ipF2.pack(pady=2,fill=X,padx=2)

        Label(self.ipF2, text='导入excel:').pack(side=LEFT, padx=2, pady=2)
        self.pathIpt = Entry(self.ipF2)
        self.pathIpt.pack(side=LEFT, padx=2, pady=2)
        Button(self.ipF2,text='选择文件',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)
        Label(self.ipF2, textvariable=self.statustxt,fg='red',width=2).pack(side=LEFT, padx=2, pady=2) 
        btnFra0=Frame(self)
        btnFra0.pack(pady=2,fill=X,padx=2)
        Button(btnFra0,text='生成任务_卡价格',command=lambda:create_dj2(self.fpath)).pack(side=LEFT,fill=X,padx=2)


    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.pathIpt.delete(0, END)
            self.pathIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt.set(max_row-1)
        else:
            messagebox.showerror('错误提示','请选择excel文件')

class DCHihtKwFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.pack(fill=X,padx=5)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.createWidgets()

    def createWidgets(self):
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)

        f01=Frame(f0)
        f01.pack(fill=X,padx=2,pady=2)

        Label(f01, text='Impression大于:').pack(side=LEFT, padx=2, pady=2)
        self.ImpressionIpt = Entry(f01,width=10)
        self.ImpressionIpt.pack(side=LEFT, padx=2, pady=2)

        Label(f01, text='Clicks大于:').pack(side=LEFT, padx=2, pady=2)
        self.ClicksIpt = Entry(f01,width=10)
        self.ClicksIpt.pack(side=LEFT, padx=2, pady=2)

        f02=Frame(f0)
        f02.pack(fill=X,padx=2,pady=2)

        Label(f02, text='ExpensePerClick小于:').pack(side=LEFT, padx=2, pady=2)
        self.ExpensePerClickIpt = Entry(f02,width=10)
        self.ExpensePerClickIpt.pack(side=LEFT, padx=2, pady=2)

        Label(f02, text='ACOS小于:').pack(side=LEFT, padx=2, pady=2)
        self.ACOSIpt = Entry(f02,width=10)
        self.ACOSIpt.pack(side=LEFT, padx=2, pady=2)

        f03=Frame(f0)
        f03.pack(fill=X,padx=2,pady=2)
        Label(f03, text='DirectConversions大于:').pack(side=LEFT, padx=2, pady=2)
        self.DirectConversionsIpt = Entry(f03,width=10)
        self.DirectConversionsIpt.pack(side=LEFT, padx=2, pady=2)

        self.btn_dc_yzkw=Button(f03,text='导出优质广告关键词',command=lambda:Thread(target=self.start_dc).start(),fg='#ffffff',bg='green',width=20)
        self.btn_dc_yzkw.pack(side=LEFT,padx=2, pady=2)
    
    def start_dc(self):
        self.btn_dc_yzkw.config(state=DISABLED)

        sqlstr='''
                select
                PlacementKeyword,
                SearchCount,
                Impression,
                Clicks,
                ExpensePerClick,
                [ACOS],
                DirectConversions
                from adpro
                where 
                id in (select min(id) from adpro group by PlacementKeyword)
                
            '''
        Impression=self.ImpressionIpt.get().strip()
        Clicks=self.ClicksIpt.get().strip()
        ExpensePerClick=self.ExpensePerClickIpt.get().strip()
        acos=self.ACOSIpt.get().strip()
        DirectConversions=self.DirectConversionsIpt.get().strip()
        
        if Impression:
            sqlstr+=f'and Impression>{Impression} '
        if Clicks:
            sqlstr+=f'and Clicks>{Clicks} '
        if ExpensePerClick:
            sqlstr+=f'and ExpensePerClick<{ExpensePerClick} '
        if acos:
            sqlstr+=f'and [ACOS]<{acos} '
        if DirectConversions:
            sqlstr+=f'and DirectConversions>{DirectConversions} '
        
        sqlstr+='order by DirectConversions desc'

        fname=sqlToExcel('优质广告关键词',self.ms,sqlstr)

        if fname:
            print(f'成功导出优质关键词:《{fname}》')
            messagebox.showinfo('提示',f'成功导出优质关键词:《{fname}》')
        
        else:
            print('导出失败')

        self.btn_dc_yzkw.config(state=NORMAL)

# import tkinter
# list_win=tkinter.Tk()
# list_win.wm_geometry("800x650")
# list_win.title('ADMIN2')
# Data4Fra(list_win).pack(fill=X,padx=3,pady=3)
# list_win.mainloop()